﻿using ExcelHelper;
using ExcelHelper.ExcelModel;
using Microsoft.Extensions.DependencyInjection;
using SqlSugar;
using SqlSugarAndEntity;
using SqlSugarAndEntity.Entity.wxx;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace UpdateDatabase
{
    public class WXXExecuteModule
    {
        private IBaseMethod SqlSugarProvider;
        public WXXExecuteModule()
        {
            IServiceCollection services = new ServiceCollection();
            services.AddSingleton<IBaseMethod, BaseMethod>();
            IServiceProvider serviceProvider = services.BuildServiceProvider();
            SqlSugarProvider = serviceProvider.GetService<IBaseMethod>();
        }
        /// <summary>
        /// 微主题导入方法
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        /// <param name="SheetName">sheet名称</param>
        public void DoImportWXXtopic(string FilePath,string SheetName)
        {
            ISqlSugarClient db = SqlSugarProvider.Db();
            NpoiHelper npoi = new NpoiHelper();
            List<WXXModel> list = npoi.ExcelToObject<WXXModel>(FilePath, SheetName);
            var Check = CheckExcelData(list, 4);
            try
            {
                if (Check.Result)
                {
                    //开启事务,下面左右的操作全部用事务进行
                    db.Ado.BeginTran();
                    //第一步,插入excel里面数据库里面没有的作者
                    InsertAuthorInfo(db, list);
                    //第二部,插入excel里面数据库没有的课程
                    InsertCourseInfo(db, list);
                    //第三部，修改execl数据源，吧名称替换成为id
                    list = ExchangeModelInfo<WXXModel,wxx_catalog_topics>(db, list);
                    //第四步,插入视频信息
                    list = InsertVideo(db, list);
                    //第五步，插入视频和课程关系
                    InsertVideo_r_Course(db, list);
                    //第六步,插入课程和分类关系表
                    InsertCourse_r_catalog<WXXModel, wxx_catalog_topics>(db, list);
                    //第七步,插入视频和作者关系表
                    InsertVideoRAuthor(db, list);
                    //提交事务
                    int count = GetInsertDataTotal<wxx_catalog_topics>(db);
                    if (list.Count != count)
                    {
                        Console.WriteLine($"excel数据为{list.Count}条,导入的数据为{count}条,数据回滚!");
                        db.Ado.RollbackTran();
                    }
                    else
                    {
                        Console.WriteLine($"excel数据为{list.Count}条,导入的数据为{count}条,验证成功，提交事务");
                        db.Ado.CommitTran();
                    }
                }
                else
                {
                    Console.WriteLine(Check.ErrorMsg);
                }

            }
            catch (Exception e)
            {
                //异常就回滚
                db.Ado.RollbackTran();
                Console.WriteLine(e.Message);
            }
        }

        /// <summary>
        /// 微分类导入
        /// </summary>
        /// <param name="FilePath"></param>
        /// <param name="SheetName"></param>
        public void DoImportWXXFZ(string FilePath, string SheetName)
        {
            ISqlSugarClient db = SqlSugarProvider.Db();
            NpoiHelper npoi = new NpoiHelper();
            List<WXXModel> list = npoi.ExcelToObject<WXXModel>(FilePath, SheetName);
            var Check = CheckExcelData(list, 4);
            try
            {
                if (Check.Result)
                {
                    //开启事务,下面左右的操作全部用事务进行
                    db.Ado.BeginTran();
                    //第一步,插入excel里面数据库里面没有的作者
                    InsertAuthorInfo(db, list);
                    //第二部,插入excel里面数据库没有的课程
                    InsertCourseInfo(db, list);
                    //第三部，修改execl数据源，吧名称替换成为id
                    list = ExchangeModelInfo<WXXModel, wxx_catalog_fzcourse>(db, list);
                    //第四步,插入视频信息
                    list = InsertVideo(db, list);
                    //第五步，插入视频和课程关系
                    InsertVideo_r_Course(db, list);
                    //第六步,插入课程和分类关系表
                    InsertCourse_r_catalog<WXXModel, wxx_catalog_fzcourse>(db, list);
                    //第七步,插入视频和作者关系表
                    InsertVideoRAuthor(db, list);
                    //提交事务
                    int count = GetInsertDataTotal<wxx_catalog_fzcourse>(db);
                    if (list.Count != count)
                    {
                        Console.WriteLine($"excel数据为{list.Count}条,导入的数据为{count}条,数据回滚!");
                        db.Ado.RollbackTran();
                    }
                    else
                    {
                        Console.WriteLine($"excel数据为{list.Count}条,导入的数据为{count}条,验证成功，提交事务");
                        db.Ado.CommitTran();
                    }
                }
                else
                {
                    Console.WriteLine(Check.ErrorMsg);
                }

            }
            catch (Exception e)
            {
                //异常就回滚
                db.Ado.RollbackTran();
                Console.WriteLine(e.Message);
            }
        }



        private void InsertAuthorInfo<T>(ISqlSugarClient db, List<T> list) where T : BaseExcelModel
        {
            //去重获取excel里面的所有作者信息
            var ExcelAuthors = list
                .GroupBy(t => new { t.Teacher })
                .Select(t => new wxx_authors
                {
                    AuthorName = t.First().Teacher,
                    ShortName = CheckStringChinese(t.First().Teacher) ? $"{NPinyin.Pinyin.GetPinyin(t.First().Teacher).Substring(0,1)}" : t.First().Teacher,
                    AuthorResume = t.First().TeachInfo,
                    Capture = t.First().TeacherPicture,
                })
                .ToList();
            var x = db.Storageable(ExcelAuthors).WhereColumns(t => new { t.Capture }).ToStorage();
            x.AsInsertable.ExecuteCommand();
        }
        public static bool CheckStringChinese(string text)
        {
            bool res = false;
            foreach (char t in text)
            {
                if ((int)t > 127)
                    res = true;
            }
            return res;
        }
        /// <summary>
        /// 插入课程信息
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换后的list</param>
        private void InsertCourseInfo<T>(ISqlSugarClient db, List<T> list) where T : BaseExcelModel
        {
            List<wxx_courses> courses = list
                .GroupBy(t => new { t.CourseName, t.CoursePicture, t.CourseInfo })
                .Select(t => new wxx_courses
                {
                    Title = t.FirstOrDefault().CourseName,
                    ImagePath = t.FirstOrDefault().CoursePicture.Split(".")[0],
                    Infos = t.FirstOrDefault().CourseInfo,
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now,
                    IsDelete = 0,
                    KeyWords = t.FirstOrDefault().KeyWords
                }).ToList();
            var x = db.Storageable(courses).WhereColumns(t => new { t.ImagePath }).ToStorage();
            x.AsInsertable.ExecuteCommand();
        }
        /// <summary>
        /// 对excel解析出来的数据进行变更，把需要变更为id的字段进行替换，提升后面的效率
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">直接解析出来的数据</param>
        /// <returns>id替换之后的数据</returns>
        private List<T> ExchangeModelInfo<T,T1>(ISqlSugarClient db, List<T> list) 
            where T : BaseExcelModel
            where T1:wxx_catalog
        {
            //获取今天新插入的课程信息
            var CourseImgs = list.Select(t => t.CoursePicture.Split(".")[0]);
            //获取EXCEL内全部的课程
            List<wxx_courses> courses = db.Queryable<wxx_courses>().Where(t => CourseImgs.Contains(t.ImagePath)).ToList();
            var AuthorImgs = list.Select(t => t.TeacherPicture);
            var AuthorList = db.Queryable<wxx_authors>().Where(t => AuthorImgs.Contains(t.Capture)).ToList();
            var CataList = db.Queryable<T1>().ToList();
            list.ForEach(t =>
            {
                //把课程名称变成ID
                t.CourseID = courses.Where(a => a.ImagePath == t.CoursePicture.Split(".")[0]).Select(a => a.Id).FirstOrDefault();
                //把讲师名称替换成ID
                t.AuthorID = AuthorList.Where(a => a.Capture == t.TeacherPicture).Select(a => a.Id).FirstOrDefault();
                //把最后一级目录名称替换成ID
                //把三级目录名称替换成ID
                t.CatalogID = CataList.Where(a =>
                {
                    var CataValue = typeof(T).GetProperty($"Level4").GetValue(t).ToString();
                    return a.CatalogName == CataValue && a.CatalogLevel == 2;
                })
                .Select(a=>a.Id)
                .FirstOrDefault();
            });
            return list;
        }

        /// <summary>
        /// 插入视频数据,并将数据源list的videoid修改
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换过id后的list</param>
        private List<T> InsertVideo<T>(ISqlSugarClient db, List<T> list) where T : BaseExcelModel
        {
            List<wxx_videos> videos = list.
                GroupBy(t => new
                {
                    t.VideoCode,
                    t.VideoPicture,
                }).
                Select(t =>
                {
                    if (!double.TryParse(t.FirstOrDefault().Duration, out double value))
                    {
                        value = 0;
                    }
                    return new wxx_videos
                    {
                        Title = t.FirstOrDefault().TitleName,
                        VideoCode = t.FirstOrDefault().VideoCode,
                        Capture = t.FirstOrDefault().VideoPicture,
                        Infos = t.FirstOrDefault().KeyWords,
                        Duration = value,
                        V_Width_Height = t.FirstOrDefault().Resolving,
                        CreateTime = DateTime.Now,
                        PublishTime = DateTime.Now,
                        KeyWords = t.FirstOrDefault().KeyWords,
                        FileFormat=t.FirstOrDefault().VideoCode.Split(".")[1],
                        OriginalVideoCode= t.FirstOrDefault().VideoCode.Split(".")[0],
                    };
                }).ToList();
            var s = db.Storageable(videos).WhereColumns(t => new { t.VideoCode }).ToStorage();
            s.AsInsertable.ExecuteCommand();
            //把视频主键ID全部改为videoid
            //var sql = db.Updateable<videos>().SetColumns(t => t.Id == t.VideoId).Where(t => SqlFunc.DateIsSame(t.CreateTime, DateTime.Now)).ExecuteCommand();
            var videosCodes = videos.Select(t => t.VideoCode);
            videos = db.Queryable<wxx_videos>().Where(t => t.VideoCode.Contains(t.VideoCode)).ToList();
            list.ForEach(t =>
            {
                t.ViedoId = videos.Where(a => a.VideoCode == t.VideoCode).Select(a => a.Id).FirstOrDefault();
            });
            return list;
        }

        private void InsertVideoRAuthor<T>(ISqlSugarClient db, List<T> list) where T : BaseExcelModel
        {
            List<wxx_video_r_author> vra = list.GroupBy(t => new { t.ViedoId, t.AuthorID })
                .Select(t =>
                {
                    var item = t.FirstOrDefault();
                    return new wxx_video_r_author()
                    {
                        VideoId = item.ViedoId,
                        AuthorId = item.AuthorID,
                    };
                }).ToList();
            var s = db.Storageable(vra).WhereColumns(t => new { t.VideoId, t.AuthorId }).ToStorage();
            s.AsInsertable.ExecuteCommand();
        }
        /// <summary>
        /// 插入视频和课程之间的关系
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换后的list数据</param>
        private void InsertVideo_r_Course<T>(ISqlSugarClient db, List<T> list) where T : BaseExcelModel
        {
            //获取今天新插入的课程信息
            //List<courses> courses = baseMethod.Db().Queryable<courses>().Where(t => SqlFunc.DateIsSame(t.CreateTime, DateTime.Now)).ToList();
            List<wxx_video_r_course> vrc = list.
                GroupBy(t => new
                {
                    t.ViedoId,
                    t.CourseID,
                }).
                Select(t => new wxx_video_r_course
                {
                    VideoId = t.FirstOrDefault().ViedoId,
                    CourseId = t.FirstOrDefault().CourseID,
                }).ToList();
            var s = db.Storageable(vrc).WhereColumns(t => new { t.VideoId, t.CourseId }).ToStorage();
            s.AsInsertable.ExecuteCommand();
            //db.Insertable(vrc).ExecuteCommand();
        }
        /// <summary>
        /// 插入课程和目录的关系
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换后的list数据</param>
        private void InsertCourse_r_catalog<T,T1>(ISqlSugarClient db, List<T> list)
            where T : BaseExcelModel
            where T1:wxx_catalog
        {
            int type = 0;
            if (typeof(T1) == typeof(wxx_catalog_fzcourse))
            {
                type = 3;
            }
            else if(typeof(T1) == typeof(wxx_catalog_topics))
            {
                type = 1;
            }
            List<wxx_course_r_catalog> crc = list.
                GroupBy(t => new
                {
                    t.CatalogID,
                    t.CourseID
                }).
                Select(t => new wxx_course_r_catalog
                {
                    CourseId = t.FirstOrDefault().CourseID,
                    CatalogId = t.FirstOrDefault().CatalogID,
                    CatalogType=type
                }).ToList();
            var s = db.Storageable(crc).WhereColumns(t => new { t.CourseId, t.CatalogId }).ToStorage();
            s.AsInsertable.ExecuteCommand();
        }

        /// <summary>
        /// 检查excel元素是否正确
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="MaxLevel"></param>
        /// <returns></returns>
        private (bool Result, string ErrorMsg) CheckExcelData<T>(List<T> list, int MaxLevel) where T : BaseExcelModel
        {
            var CheckVideo = ValidateVideoCodeAndVideoPic(list);
            if (!CheckVideo.Result)
            {
                return CheckVideo;
            }
            var CheckCourse = ValidateCourseNameAndCoursePic(list);
            if (!CheckCourse.Result)
            {
                return CheckCourse;
            }
            var CheckCatalog = ValidateCatalogAndCoursePic(list, MaxLevel);
            if (!CheckCatalog.Result)
            {
                return CheckCatalog;
            }
            return (true, "");
        }
        /// <summary>
        /// 检验一个videoCode是否对应一个videopic
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        private (bool Result, string ErrorMsg) ValidateVideoCodeAndVideoPic<T>(List<T> list) where T : BaseExcelModel
        {
            bool Result = true;
            string ErrorMsg = string.Empty;
            //excel总数据
            int TotalCount = list.Count;
            //检查video和videopic是否为唯一对应关系
            int VideoCount = list.GroupBy(t => new { t.VideoCode, t.VideoPicture }).Count();
            if (TotalCount != VideoCount)
            {
                //检查videocode和pic是不是存在不唯一对应关系
                var ErrorVideo = list
                    .Where(a => list.Where(b => (a.VideoCode == b.VideoCode && a.VideoPicture != b.VideoPicture) || (a.VideoCode != b.VideoCode && a.VideoPicture == b.VideoPicture)).Any())
                    .GroupBy(a => new { a.VideoCode, a.VideoPicture })
                    .Select(a => new { a.FirstOrDefault().VideoCode, a.FirstOrDefault().VideoPicture });
                foreach (var item in ErrorVideo)
                {
                    Result = false;
                    ErrorMsg += $"请检查excel，以下videocode和videopic匹配不唯一:{item.VideoCode}:{item.VideoPicture}";
                }
            }
            return (Result, ErrorMsg);
        }
        /// <summary>
        /// 检验一个课程名是否对应一个课程图片
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        private (bool Result, string ErrorMsg) ValidateCourseNameAndCoursePic<T>(List<T> list) where T : BaseExcelModel
        {
            bool Result = true;
            string ErrorMsg = string.Empty;
            var ErrorCourse = list
                .Where(a => list.Where(b => (b.CourseName == a.CourseName && b.CoursePicture != a.CoursePicture) || (b.CourseName != a.CourseName && b.CoursePicture == a.CoursePicture)).Any())
                .GroupBy(a => new { a.CourseName, a.CoursePicture })
                .Select(a => new { a.FirstOrDefault().CourseName, a.FirstOrDefault().CoursePicture });
            if (ErrorCourse.Any())
            {
                foreach (var item in ErrorCourse)
                {
                    Result = false;
                    ErrorMsg += $"请检查excel，以下课程名称和课程图片匹配不唯一！:{item.CourseName}:{item.CoursePicture}";
                }
            }
            return (Result, ErrorMsg);
        }
        /// <summary>
        /// 检验一个课程是否对应一个目录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="MaxLevel"></param>
        /// <returns></returns>
        private (bool Result, string ErrorMsg) ValidateCatalogAndCoursePic<T>(List<T> list, int MaxLevel) where T : BaseExcelModel
        {
            string MaxLevelStr = $"Level{MaxLevel}";
            bool Result = true;
            string ErrorMsg = string.Empty;
            var Selector = ExpressionHelper.CreateSelectExpression<T, GroupCataAndCourseModel>(
                new Dictionary<string, string>()
                {
                    { MaxLevelStr,"LevelMax" },
                    { "CoursePicture","CoursePicture" }
                });
            //检查一个课程下是不是只有一个分类
            var GroupLevel = list.Select(Selector.Compile()).Distinct(new Compare());
            foreach (var course in GroupLevel)
            {
                var select = GroupLevel
                    .Where(t => t.CoursePicture == course.CoursePicture);
                if (select.Count() > 1)
                {
                    foreach (var item in select)
                    {
                        ErrorMsg += $"请检查excel，以下课程图片和分类匹配不唯一！:{item.LevelMax}:{item.CoursePicture}";
                    }
                }
            }
            return (Result, ErrorMsg);
        }
        private int GetInsertDataTotal<T>(ISqlSugarClient db) where T :wxx_catalog
        {
            int type = 0;
            if (typeof(T) == typeof(wxx_catalog_fzcourse))
            {
                type = 3;
            }
            else if (typeof(T) == typeof(wxx_catalog_topics))
            {
                type = 1;
            }
            var count = db.Queryable<wxx_videos,wxx_video_r_author , wxx_authors, wxx_video_r_course, wxx_courses, wxx_course_r_catalog, T> (
                (a, b, c, d, e, f, g) => new JoinQueryInfos(
                  JoinType.Inner, a.Id == b.VideoId,
                  JoinType.Inner, b.AuthorId == c.Id,
                  JoinType.Inner, a.Id == d.VideoId,
                  JoinType.Inner, d.CourseId == e.Id,
                  JoinType.Inner, e.Id == f.CourseId && f.CatalogType== type,
                  JoinType.Inner, g.Id==f.CatalogId
                ))
                .Where((a, b, c, d, e, f, g) => SqlFunc.DateIsSame(a.CreateTime, DateTime.Now))
                .Count();
            return count;
        }
    }
}
